create table GalacticUsers
(
	Id		int	not null identity(1,1),
	Username varchar(30) not null,	
	PasswordHash text not null,
	DomainUser text null,
	PushoverKey text null,
	Email text not null	
);

go

alter table GalacticUsers add constraint pk_galacticusers_id primary key(id) with fillfactor=90;
go

alter table GalacticUsers add constraint uq_galacticusers_username unique(Username);
go

create proc usp_AddGalacticUser
@Username varchar(30),
@PasswordHash text,
@Email text,
@DomainUser text = null,
@PushoverKey text = null
AS

	insert into GalacticUsers(Username, PasswordHash, Email, DomainUser, PushoverKey)
		values(@username, @PasswordHash, @Email, @DomainUser, @PushoverKey);

GO


create proc usp_GetUsers
AS

	SELECT
		Username,
		Email,
		PushoverKey,
		DomainUser

		from GalacticUsers

		order by Username ASC

GO


create proc usp_Login
@Username varchar(30),
@PasswordHash text
AS

	select 
		Username,
		Email,
		DomainUser,
		PushoverKey
	from GalacticUsers

	where username=@Username and PasswordHash like @PasswordHash;


GO


create table Config
(
	ConfigKey varchar(30) not null,
	ConfigValue varchar(128) not null
)
go

alter table Config add constraint uq_config_configkey unique(configkey);
go


create proc usp_GetConfigValue
@ConfigKey varchar(30)
AS

	select * from config where configkey = @ConfigKey;
	
GO

create proc usp_SetConfigValue
@ConfigKey varchar(30),
@ConfigValue varchar(128)
AS

	delete from Config where ConfigKey = @ConfigKey;

	insert into config(ConfigKey, ConfigValue) values(@ConfigKey, @ConfigValue);


GO

create proc usp_GetPushoverKey
@Username varchar(30)
AS

	select
		PushoverKey
		
		from GalacticUsers
		where Username = @Username

GO

create proc usp_GetUserEmail
@Username varchar(30)
AS

	select Email from GalacticUsers where username = @Username;

GO


create table DataSets
(
	Id int not null identity(1,1),
	Name varchar(30) not null,
	Loaded bit not null default(0),
	ParserRegex text not null
)
go

alter table DataSets add constraint pk_dataset_id primary key(Id);
alter table datasets add constraint uq_dataset_name unique(Name);
go

create table Data
(
	Id bigint not null identity(1,1),
	DataSetId int not null,
	GalaxyName varchar(30) not null,
	ra decimal not null,
	decl decimal not null,
	Morphology varchar(30) not null
)
go

alter table Data add constraint pk_data_id primary key(id);
alter table data add constraint fk_data_dataset_datasetid foreign key(DataSetId) references DataSets(Id);
go



---

create proc usp_AddDataSet
@Name varchar(30)
AS

	INSERT INTO DataSets(Name) Values(@name);
	select SCOPE_IDENTITY() AS Id;
GO


create proc usp_GetDataSets
AS

	SELECT ID, Name, Loaded from DataSets
	order by DataSets.Name ASC;
	
GO


create proc usp_AddGalaxyData
@DataSetId	bigint,
@GalaxyName varchar(30),
@ra decimal,
@decl decimal,
@Morphology varchar(30)
AS

	insert into Data(DataSetId, GalaxyName, ra, decl, Morphology)
		Values(@DataSetId, @GalaxyName, @ra, @decl, @Morphology);
	
GO



alter table DataSets add Regex text null;
go
alter table DataSets add FieldMap text null;
go


create proc usp_SetParseSettings
@DataSetId int,
@Regex text,
@FieldMap text
AS

	update DataSets set regex=@Regex, FieldMap=@FieldMap 
		where Id = @DataSetId;

GO


create table DataSetLoadRequest
(
	id int not null identity(1,1),
	DataSetId int not null,
	UserId	int	not null,
	FilePath text not null,
	Processing bit not null default(0),
	Processed bit not null default(0),
	TotalRows bigint null,
	RowsProcessed bigint null,
	Email bit not null,
	Pushover bit not null
)
go

alter table DataSetLoadRequest add constraint pk_datasetloadrequest_id primary key(id);
alter table DataSetLoadRequest add constraint fk_datasetloadrequest_datasetid foreign key(datasetid) references DataSets(id);
alter table DataSetLoadRequest add constraint fk_datasetloadrequest_userid foreign key(UserId) references GalacticUsers(id);
go

create proc usp_AddDataSetLoadRequst
@DataSetId int,
@UserId int,
@FilePath text,
@Email bit,
@Pushover bit
AS

	insert into DataSetLoadRequest(DataSetId, UserId, FilePath, Email, Pushover)
		values(@DataSetId, @UserId, @FilePath, @email,	@Pushover);
	
	select SCOPE_IDENTITY() AS Id;

GO


create proc usp_GetNearbyObjects
@startobject bigint,
@distance float -- degrees
as

declare @distanceRadians float = radians(@distance);
declare @g geometry;
declare @ra1 float;
declare @decl1 float;
declare @dataset bigint;

select 
	@g = data.GeoPosition,
	@ra1 = data.ra,
	@decl1 = data.decl, 
	@dataset = data.DataSetId

 from data where data.id = @startobject

declare @radius geometry;
set @radius = @g.STBuffer(1);

	select 
	
	ROW_NUMBER() over (order by degrees(data.GeoPosition.STDistance(@g))) - 1 AS rownum,

	data.id,
	data.GalaxyName,
	data.ra,
	data.decl,
	degrees(data.GeoPosition.STX) as XDegrees,
	degrees(data.GeoPosition.STY) as YDegrees,	
	degrees(data.GeoPosition.STDistance(@g)) AS STDistanceDegrees

	from data

	where 		
	data.DataSetId = @dataset
	and
	data.GeoPosition.STDistance(@g) <  @distanceRadians
	

	order by STDistanceDegrees ASC
	
	go


	----------


	create table DataRunRequests
	(
		id	int	not null identity(1,1),
		DataSetId int not null,
		Distance float not null,
		Completed bit not null default(0),
		Progress float not null default(0)
	);

	go

	alter table DataRunRequests add constraint fk_datarunreqeusts_datasets_datasetid foreign key(DataSetId) references DataSets(Id);
	go



	create table DataRunResultsD
	(
		ResultId bigint not null identity(1,1),
		RunId int not null,
		
	)






alter proc usp_GetStatusOfPendingDataRuns
AS

select ProcessedObjects, SourceObjects, 
case when SourceObjects > 0 then (1.0 * ProcessedObjects / SourceObjects) else 0.0 end AS Perc, 
Title, DataRunRequests.StartedProcessing,
	DataRunRequests.SourceFilter,
	DataRunRequests.NeighborFilter,
	DataRunRequests.Distance,
	DataSets.Name as DataSetName,
	Pushover,
	Email

from DataRunRequests 

inner join DataSets on Datasets.id = DataRunRequests.DataSetId

where DataRunRequests.Completed = 0 and StartedProcessing is not null


GO



-----


create spatial index ix_geoposition on data
(
	GeoPosition
)
with 
( 
 BOUNDING_BOX = ( XMIN = 0.0, XMAX = 360.0, YMIN = -90.0, YMAX = 90.0 )
)

go